串刺し計算(統合)
1 串刺し計算(統合)の種類
1 串刺し計算(統合)の機能
たとえば、各支店の品名別売上を表す複数のワークシートがある場合、各支店の品名別売上を一つのワークシートに集計する処理を一般に「串刺し計算」計算と呼んでいます。Excelではこの処理を「統合」といいます。
下表は、(L1)〜(L3)のワークシートの各支店データを(L4)の支店合計シートに統合によりした結果です。
2 統合の種類
データの統合を行う方法には、リストの形式・ワークシート上のリストの配置場所などによっていくつかの種類があります。
(1) 「コピー」・「形式を選択して貼り付け」による加算
各シートのデータをコピーして、「形式を選択して貼り付け」の演算機能の「加算」により集計シートに貼り付けて集計する方法です。(詳細はこちら)
(2) 3D参照(数式)による統合
複数のワークシートを参照する数式のことを「3D参照」といいます。3D参照の数式は次のように表されます。「=SUM(統合元1!B3,統合元2!B3,統合元3!B3)」。これは、「統合元1」〜「統合元3」のそれぞれのセルB3を合計する計算式を表しています。(詳細はこちら)
統合元範囲のデータ(加算対象となるデータ)の並び方や配置がワークシートによって異なるような場合は3D参照(数式)による統合を利用します。
(3) メニューの「データ(D)」「統合(N)」による統合
Excelのメニューの「データ(D)」「統合(N)」を利用して統合を行う方法で、データの状態によって「位置による統合」と「項目による統合」があります。
「位置による統合」は、統合元範囲のデータがすべてワークシートの同じ位置、同じ順序で並んでいる場合に利用します。(詳細はこちら)
「項目による統合」は、統合元範囲のデータの並び方や配置が多少異なっているが、行見出しや列見出しが同じ複数のワークシートを集計する場合に利用します。(詳細はこちら)
どちらの方法も元データが変更されると、自動的に統合結果が更新されるように設定することができますが、統合に含まれるセルや範囲を変更することはできません。また、手動で統合を更新することもでき、この場合は、統合に含まれるセルや範囲を変更することができます。


2 「コピー」・「形式を選択して貼り付け」による加算(統合)
累計計算や串刺し計算(統合)で最も簡単な方法に、データをコピーして、メニューの「形式を選択して貼り付け」を利用する方法があります。
1. 串刺し計算をするシート(統合元シート)のデータ領域を選択します。
2. 選択範囲内で右クリックして、ショートカットメニューの「コピー(C)」を選択します。
3. 集計用のリスト(統合先シート)のデータ部分の左上端となるセルを右クリックして、ショートカットメニューの「形式を選択して貼り付け(S)」を選択します。
4. 「形式を選択して貼り付け」ダイアログボックスの貼り付け欄で「値(V)」のオプションボタンと演算欄で「加算(D)」のオプションボタンをオンにします。
5. 「OK」ボタンをクリックします。
6. 上記の1〜5を統合するワークシートの数分繰返します。2回目以降のコピー、貼り付けを行うと前回のコピー結果に貼り付けたデータが加算されます。


3 3D参照 (計算式) による統合
毎月のデータを年間集計表にまとめるように、統合元範囲のデータがすべて同じレイアウトで作成されている場合は、シート名を使って3D参照による串刺し計算を行うことができます。
この場合、月報に相当するシートを「参照元シート」、年間集計表に相当するシートを「参照先シート」と言います。
1 参照元シートが連続している場合の統合
計算対象となるワークシートが、同一ブック内に連続して並んでいる場合の計算方法です。
1. 参照先シート見出しタブをクリックします。
2. 合計を算出する領域の左上端セル(B3)を選択して「オートSUM」ボタンをクリックすると、セルに「=SUM()」と計算式が表示されます
。(下図)

|

|
「オートSUM」ボタン |
3. 計算対象の最初の参照元シート見出しタブをクリックします。(下左図)
4. 計算対象シートの終わりの参照元シート見出しタブを「Shift」キーを押しながらクリックすると、最初にクリックしたシートから終わりの参照シートまでが選択されます。(下右図)
(画面に表示されているシートは、最初の参照元シートのままになっている)
5. 表示されているシート(最初の参照シート)で、合計を算出する領域の左上端セル(B3セル)をクリックしてから「Enter」キーを押すと、参照先シートのB3セルに合計が表示されます。(下左図)
なお、セルB3をクリックすると、数式バーで入力された計算式(下右図)を見ることが出来ます。数式の意味は、シート「統合元1」からシート「統合元3」までのセルB3を合計する、を表しています。
6. セルB3をク選択し、右下隅のフィルハンドルを下にドラッグしてデータ範囲の下端まで計算式を複写します。
7. 上記でコピーした範囲を選択し、選択範囲右下隅セルのフィルハンドルを右にドラッグしてデータ範囲の右端まで計算式を複写します。下図は計算式複写後の参照先シートの内容です。
2 参照元シートが並んでいない場合の統合
計算対象となる参照先シートが、連続して並んでいない場合の計算方法です。図は上記とほぼ同様ですので省略します。
1. 参照先シート見出しタブをクリックします。
2. 合計を算出する領域の左上端セル(B3)を選択して「オートSUM」ボタンをクリックすると、セルに「=SUM()」と計算式が表示されます
。
3. 計算対象の最初の参照元シート見出しタブをクリックします。
4. 参照先シートの計算式入力セルと同じ位置のセル(B3)をクリックすると、数式バーに最初の参照元シート名とセル番地が表示されますので、右括弧の前に「
, (カンマ)」を入力します。
なお、最後の参照元シートのセルをクリックしたときは、次に「
, (カンマ)」を入力しないでください。
5. 次の参照元シート見出しをクリックして、上記 4
と同様の処理を行い、この処理を計算する参照元シート回数分繰返します。
6. 最後の参照元シートのセルをクリックしたら「Enter」キーを押すと、参照先シートに計算式が入力されます。
7. セルB3をク選択し、右下隅のフィルハンドルを下にドラッグしてデータ範囲の下端まで計算式を複写します。
8. 上記でコピーした範囲を選択し、選択範囲右下隅セルのフィルハンドルを右にドラッグしてデータ範囲の右端まで計算式を複写します。


4 同一形式リストの統合
統合元のデータがすべて同じ位置、同じ順序で並んでいる同一形式リストの場合は、「位置による統合」を行います。
位置による統合を行うには、統合元のデータがリスト形式(先頭行には列見だしがあり、各シートの列見だし、行見出しに同じ項目名が入力されている。)になっている必要があります。
また、統合元範囲の列見出しや行見出しは統合先範囲にコピーされませんので統合先ワークシートに見だしを表示するには、統合元シートのいずれかから見だしを複写するか、入力する必要があります。
下表は、同じ形式の(L1)〜(L3)ワークシートの各支店データを(L4)の支店合計シートに統合によりした結果です。
1. 統合先範囲の左上隅のセルをクリックします。
下図では、予め見だしをコピーしてありますので、データ部分の左上端セル(B3)をクリックします。
2. メニューの「データ(D)」「統合(N)」を選択します。
3. 「統合の設定」ダイアログボックス(下図)の[集計の方法(F)] ボックスの統合に使う集計方法をクリックします。集計方法は、「合計、データの個数、平均、最大値、最小値、積、数値の個数、標本標準偏差、標準偏差、標本分散、分散」の11種類です。
4. 「統合元範囲(R)」ボックスの右端にあるダイアログボックス縮小ボタン(赤い矢印の部分)をクリックするとダイアログボックスが折りたたまれますので、統合元シートの統合するセル範囲を指定します。
下図では、リストのデータ部分(セルB3:G7)のみの範囲指定を行っています。
5. ダイアログボックス縮小ボタン(赤い矢印の部分)をクリックすると、ダイアログボックスが元の大きさに戻り、「統合元範囲(R)」ボックスに選択範囲が表示されているのを確認して、「追加(A)」ボタンをクリックすると「統合元(E)」ボックスに指定結果が追加されます。(下図)
6. すべての統合元シートの範囲について手順 4 〜 5 を繰り返します。下図は、その結果です。
7. 「統合の基準」の「上端行(T)」「左端列(L)」のチェックボックスはオフにしておきます。
統合元データの変更に応じて統合テーブルが自動的に更新されるようにするには、「統合元データとリンクする」チェック ボックスをオンにします。いったんリンクを設定すると、統合されたデータを含む統合元範囲に新規に追加したり、変更を加えたりすることはできません。
下図は、データの統合結果です。


5 見出しが異なる場合の統合
統合元範囲のデータの並び方や配置は異なっていても、行見出しや列見出しが同じ複数のワークシートを集計する場合は、「項目による統合」を行います。
項目による統合を行うには、統合元のデータがリスト形式(先頭行には列見だしがあり、各シートの列見だし、行見出しに同じ項目名が入力されている。)になっている必要があります。
また、統合の結果、統合元範囲の列見出しや行見出しも統合先範囲にコピーされます。
下表は、同じ形式の(L1)〜(L3)ワークシートの各支店データを(L4)の支店合計シートに統合します。
1. 統合先範囲の左上隅のセル(セルA2)をクリックします。
2. メニューの「データ(D)」「統合(N)」を選択します。
3. 「統合の設定」ダイアログボックス(下図)の[集計の方法(F)] ボックスの統合に使う集計方法をクリックします。集計方法は、「合計、データの個数、平均、最大値、最小値、積、数値の個数、標本標準偏差、標準偏差、標本分散、分散」の11種類です。
4. 「統合元範囲(R)」ボックスの右端にあるダイアログボックス縮小ボタン(赤い矢印の部分)をクリックするとダイアログボックスが折りたたまれますので、統合元シートの統合するセル範囲を指定します。
下図では、リスト範囲全体(セルA2:G7)の範囲指定を行っています。
5. ダイアログボックス縮小ボタン(赤い矢印の部分)をクリックすると、ダイアログボックスが元の大きさに戻り、「統合元範囲(R)」ボックスに選択範囲が表示されているのを確認して、「追加(A)」ボタンをクリックすると「統合元(E)」ボックスに指定結果が追加されます。(下図)
6. すべての統合元シートの範囲について手順 4 〜 5 を繰り返します。下図は、その結果です。
7. 「統合の基準」の「上端行(T)」「左端列(L)」のチェックボックスをオンにします。
統合元データの変更に応じて統合テーブルが自動的に更新されるようにするには、「統合元データとリンクする」チェック ボックスをオンにします。いったんリンクを設定すると、統合されたデータを含む統合元範囲に新規に追加したり、変更を加えたりすることはできません。
下図は、データの統合結果です。

